/****************************************************************************************
This file is part of Proactive Investigator Analytics.

Proactive Investigator Analytics is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

Proactive Investigator Analytics is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with Proactive Investigator Analytics.  If not, see <http://www.gnu.org/licenses/>.

This script creates ETL_config and history tables in ETLData database.

****************************************************************************************/

USE [ETLData]
GO
/****** Object:  Table [dbo].[sysssislog]    Script Date: 05/01/2012 11:16:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sysssislog](
	[id] [int] NULL,
	[event] [nvarchar](128) NOT NULL,
	[computer] [nvarchar](128) NOT NULL,
	[operator] [nvarchar](128) NOT NULL,
	[source] [nvarchar](1024) NOT NULL,
	[sourceid] [uniqueidentifier] NOT NULL,
	[executionid] [uniqueidentifier] NOT NULL,
	[starttime] [datetime] NOT NULL,
	[endtime] [datetime] NOT NULL,
	[datacode] [int] NOT NULL,
	[databytes] [image] NULL,
	[message] [nvarchar](2048) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[smtp_hist]    Script Date: 05/01/2012 11:16:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[smtp_hist](
	[server_name] [varchar](30) NOT NULL,
	[src_file] [varchar](100) NOT NULL,
	[file_datetime] [datetime] NULL,
	[process_datetime] [datetime] NULL,
 CONSTRAINT [pk_smtp_hist] PRIMARY KEY CLUSTERED 
(
	[server_name] ASC,
	[src_file] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[smtp_delete_hist]    Script Date: 05/01/2012 11:16:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[smtp_delete_hist](
	[server_name] [varchar](30) NOT NULL,
	[src_file] [varchar](100) NOT NULL,
	[file_datetime] [datetime] NULL,
	[delete_datetime] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[proxy_hist]    Script Date: 05/01/2012 11:16:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[proxy_hist](
	[domain_name] [char](4) NOT NULL,
	[server_name] [varchar](30) NOT NULL,
	[src_file] [varchar](100) NOT NULL,
	[file_datetime] [datetime] NULL,
	[process_datetime] [datetime] NULL,
 CONSTRAINT [pk_proxy_hist] PRIMARY KEY CLUSTERED 
(
	[domain_name] ASC,
	[server_name] ASC,
	[src_file] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[proxy_delete_hist]    Script Date: 05/01/2012 11:16:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[proxy_delete_hist](
	[domain_name] [char](4) NOT NULL,
	[server_name] [varchar](30) NOT NULL,
	[src_file] [varchar](100) NOT NULL,
	[file_datetime] [datetime] NULL,
	[delete_datetime] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[proxy_date]    Script Date: 05/01/2012 11:16:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[proxy_date](
	[segment] [varchar](10) NULL,
	[domain_name] [char](4) NULL,
	[from_datetime] [datetime] NULL,
	[to_datetime] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ETL_config]    Script Date: 05/01/2012 11:16:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ETL_config](
	[ConfigurationFilter] [nvarchar](255) NOT NULL,
	[ConfiguredValue] [nvarchar](255) NULL,
	[PackagePath] [nvarchar](255) NOT NULL,
	[ConfiguredValueType] [nvarchar](20) NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[domain_hist]    Script Date: 05/01/2012 11:16:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[domain_hist](
	[domain_name] [varchar](4) NOT NULL,
	[server_name] [varchar](30) NOT NULL,
	[src_file] [varchar](100) NOT NULL,
	[file_datetime] [datetime] NULL,
	[process_datetime] [datetime] NULL,
 CONSTRAINT [pk_domain_hist] PRIMARY KEY CLUSTERED 
(
	[domain_name] ASC,
	[server_name] ASC,
	[src_file] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[dnsq_hist]    Script Date: 05/01/2012 11:16:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dnsq_hist](
	[domain_name] [varchar](4) NOT NULL,
	[server_name] [varchar](30) NOT NULL,
	[src_file] [varchar](50) NOT NULL,
	[file_datetime] [datetime] NULL,
	[process_datetime] [datetime] NOT NULL,
 CONSTRAINT [pk_dnsp_hist] PRIMARY KEY CLUSTERED 
(
	[domain_name] ASC,
	[server_name] ASC,
	[src_file] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[dnsq_delete_hist]    Script Date: 05/01/2012 11:16:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dnsq_delete_hist](
	[domain_name] [varchar](4) NOT NULL,
	[server_name] [varchar](30) NOT NULL,
	[src_file] [varchar](50) NOT NULL,
	[file_datetime] [datetime] NULL,
	[delete_datetime] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[dhcp_hist]    Script Date: 05/01/2012 11:16:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dhcp_hist](
	[domain_name] [varchar](4) NOT NULL,
	[server_name] [varchar](30) NOT NULL,
	[src_file] [varchar](100) NOT NULL,
	[file_datetime] [datetime] NULL,
	[process_datetime] [datetime] NULL,
 CONSTRAINT [pk_dhcp_hist] PRIMARY KEY CLUSTERED 
(
	[domain_name] ASC,
	[server_name] ASC,
	[src_file] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[dhcp_delete_hist]    Script Date: 05/01/2012 11:16:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dhcp_delete_hist](
	[domain_name] [varchar](4) NOT NULL,
	[server_name] [varchar](30) NOT NULL,
	[src_file] [varchar](100) NOT NULL,
	[file_datetime] [datetime] NULL,
	[delete_datetime] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
